Relational databases cannot hold sub-field information and there is no correlation in an SQL catalog with Reality multivalues and subvalues. Hence, when converting a Reality database into an SQL catalog, it may be necessary to restructure the data model in order to handle multivalues, or subvalues.
In order to restructure the data model to handle multivalues, you need to enter a code in field 6 of the column definition item associated with the multivalued attribute. This code enables the column definition to access a selected multivalue, or multivalues.
Similarly, to handle subvalues, you need to enter a code in field 7 of the column definition item associated with the subvalued attribute. This code enables the column definition to access a selected subvalue, or subvalues.
The code in field 6 of the appropriate SQL Column Definition specifies the action to be taken when the corresponding Reality attribute contains one or more multivalues. A list of codes and the actions taken are described in the table below.
The code in field 7 of the appropriate SQL Column Definition specifies the action to be taken when the corresponding Reality attribute contains more than one subvalue. The same list of codes and the actions described in the table below.
Note: Entering codes in field 6 enables access to multivalues. Entering codes in field 7 enables access to subvalues.
| Code | Action |
|---|---|
| Null | Use first multivalue or subvalue, only |
| 0 | Use all multivalues or subvalues, separated by spaces |
| n | Use nth multivalue or subvalue (where n is a positive integer) |
| E | Explode - generate a separate row for each multivalue or subvalue. |
| -1 | Use the last multivalue or subvalue |
The following Reality file item contains both multivalued and non-multivalued attributes.
| Reality Item | SQL Column Definitions |
|---|---|
ITM01 |
ID |
001
ABC]DEF]GHI |
ATTR1 |
002 A002 |
ATTR2 |
003
123]456]789 |
ATTR3 |
The SQL column definitions for Attributes 1 and 3 are setup for 'E'xploding values. The Reality file only contains one item. Selecting the table produces the following output:
SELECT * FROM T1
ID. . . . ATTR1 . . . ATTR2 . . . ATTR3 . . .ITM01 ABC A002123ITM01 DEF A002 456 ITM01 GHI A002 789 3 rows listed.
The inclusion of a column with exploding values means that there will be a corresponding row for each multivalue position in the Reality item. In addition, the values in all non-exploding columns are repeated on each row displayed. For example if Attribute 1 above contained "ABC]DEF]GHI]JKL]MNO", then the result displayed would have 5 rows. (that is, Number of Items multiplied by Maximum Number of Multivalues).
ID. . . . ATTR1 . . ATTR2 . . ATTR3 . .
ITM01 ABC A002 123 ITM01 DEF A002 456 ITM01 GHI A002 789 ITM01 JKL A002 ITM01 MNO A002 5 rows listed.
If there are no corresponding multivalues in other multivalued attributes, then NULL values are displayed for the corresponding multivalued positions as in column 'ATTR3' above.
The table is always exploded even when selecting non-exploded columns.
SELECT ID, ATTR2 FROM T1
ID. . . .ATTR1 . . . ITM01 A002 ITM01 A002 ITM01 A002
How you restructure the SQL catalog to handle a multivalued attribute depends on the type of data in the attribute. Examples of different types of multivalued data are listed below in the PERSONNEL File item.
001 Person-id 001 MR Title 002 MARK]ROBERT Forenames 003 ROBINSON Surname 004 1 HIGH STREET]STEVENAGE]SG4 1RE Address 005 MCSE]INTRODUCTION TO IMPROMPTU Qualification 006 11110]11182 Qualification date 007 MICROSOFT]COGNOS Qual. company 008 10959]9951 Post date 009 AC1]RE2 Post company code 010 10166]10023 Post Department code 011 P12]P54 Post pay code
Example of a PERSONNEL Item – Person ID 001
Note: "]" denotes a value mark.
The techniques used to handle these multivalued attributes all involve specifying the multivalue code in field 6 of the column definition. However, the exact technique used depends on the type of multivalued data. See the table Techniques for Handling Different Types of Multivalued Data for a list of possible techniques and associated examples.
Notes:
| Technique | Operation | Examples of Type of Multivalued Data |
|---|---|---|
| 1 | Set up a column to extract the first value and ignore the rest. Enter a null in column definition field 6. | Single row of data. See Example 1, sub-paragraph 1. |
| 2 | Set up a column to extract the n'th value and ignore the rest. Enter an n in column definition field 6. | Single row of data. See Example 2, sub-paragraph 1. |
| 3 | Create a single field by concatenating all the multivalues together, separated by spaces. Enter a 0 in column definition field 6. | Single row of data. See Example 1, sub-paragraph 2. |
| 4 | Create a single field by concatenating all the multivalues together, separated by commas. Use the Tfile translation code in field 8 of the column definition. | Single row of data. See Example 1, sub-paragraph 3 and Example 2, sub-paragraph 2. |
| 5 | Expand the multivalues, one per row. Enter an E in column definition field 6. | Related data. See Example 3 (step 3) and Example 4. |
| 6 | Multiple rows of multivalues containing data which is linked together can be handled by creating a 'virtual' secondary table in the main table to contain the data with one row per multivalue. You can do this using SQLM's Alternative View option and Technique 5. | Related data. See Example 3 and Example 4. |
Attributes 2 and 4 in the example PERSONNEL item contain multivalues that need to be displayed all in one row.
Attribute 2 in the PERSONNEL item contains the forenames of a person, separated by value marks. These could be handled using Techniques 1, 3 or 4. For example:
Attribute 4 in the PERSONNEL item contains the address of the person, with each part of the address separated by value marks. These can be handled in SQL using Technique 2, or in English using the Tfile translation code. For example:
Attributes 5 to 7 and 8 to 11 are two sets of multivalued attributes which contain related data. The first set (5 to 7) contains data which describes the qualifications held by the person, and when and where they where obtained. The second set (8 to 11) contains details of posts held by the person both previous and current.
The recommended method of dealing with multivalues of this type of data is to create a 'virtual' secondary table derived from the main table (Technique 6). This table will be populated by exploding the multi- or sub-valued attributes of the main table. The combination of the item-id and the multivalue (or subvalue) number can be used as the unique primary key of such a table. The example below shows how this can be achieved.
To create a 'virtual' secondary table containing the Qualifications data, proceed as follows:
Use SQLM option 2, Create Alternative View to create a new table definition QUALIFICATIONS from the PERSONNEL file, containing the columns:
PERSON_ID
QUALS
QUAL_DATE
QUAL_CO
Initially, the primary key of this table will be the same as that of its source table – PERSON_ID.
Use SQLM Option 4 to add an SQL column definition 'QUAL_NO' to the QUALIFICATIONS table, containing NV in the Attribute field (field 5).
The QUAL_NO column will form part of the unique primary key for the secondary table. The NV code will populate this column with the multivalue numbers from the QUALS, QUAL_DATE and QUAL_CO columns.
Use SQLM Option 4 to update the QUALIFICATIONS table, as follows:
E
in the Multivalue field (field 6) of the QUALS, QUAL_DATE and QUAL_CO column
definitions. This will cause the multivalues in these columns to be exploded,
thus creating a row in the QUALIFICATIONS table for each value.Assuming the PERSONNEL file contains the data listed in PERSONNEL File, the QUALIFICATIONS table will contain the following:
| PERSON_ID | QUAL_NO | QUALS | QUAL_DATE | QUAL_CO |
|---|---|---|---|---|
| 1 | 1 | MCSE | 1998-06-01 | MICROSOFT |
| 1 | 2 | INTRO. TO IMPROMPTU | 1998-08-12 | COGNOS |
| 2 | 1 | IMPROMPTU ADMIN. | 1998-06-09 | COGNOS |
| 3 | 1 |
Note: There is a primary key generated for Person ID 3, even though the person has no qualifications. This is because the column exists even though it has empty strings in its Qualification fields. This can have a beneficial effect, as inner joins will still display data from this virtual secondary table for all persons
You should also create a virtual secondary table for attributes 8 to 11 using a similar procedure to that followed for attributes 5 to 7. A secondary table is then created, similar to the following:
| PERSON_ID | POST_NO | POST_DATE | POST_CO | POST_DEPT | POST_POINT |
|---|---|---|---|---|---|
| 1 | 1 | 1998-01-01 | AC1 | 10166 | P12 |
| 1 | 2 | 1995-03-30 | RE2 | 10023 | P54 |
| 2 | 1 | 1997-06-01 | RE2 | 10023 | P54 |
| 3 | 1 | 1998-11-10 | AC1 | 10166 | P12 |